﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ProductSystem.BLL.Common
{
    public static class SQLHelperStatic
    {
        public static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ForecastDataSystemConnection"].ConnectionString;
        //public static readonly string connectionString = "data source=10.10.10.20;initial catalog=ProductSystem;user id=Develop;password=Develop";

        /// <summary>
        /// 执行sql语句，将查询语句的结果存储到DataTable并返回
        /// </summary>
        /// <param name="sqlStr">查询语句</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sqlStr)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand(sqlStr, conn);
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);
                }
            }
            catch (Exception e)
            {
                //TODO
            }
            return dt;
        }

        /// <summary>
        /// 插入DataTable中的数据到数据库中对应的表中
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="tableName">数据表名</param>
        /// <returns></returns>
        public static bool InsertData(DataTable dt, string tableName)
        {
            bool result = false;
            try
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
                {
                    sbc.BatchSize = dt.Rows.Count;
                    sbc.BulkCopyTimeout = 180;
                    sbc.DestinationTableName = tableName;
                    foreach (DataColumn item in dt.Columns)
                    {
                        sbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                    }
                    sbc.WriteToServer(dt);
                    result = true;
                }
            }
            catch (Exception e)
            {
                //TODO
                LogHelper.Instance().WriteLog(string.Format("插入数据到数据库表{0}失败！", tableName), e);
            }
            return result;
        }

        /// <summary>
        /// 插入DataTable中的数据到数据库中对应的表中
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="tableName">数据表名</param>
        /// <param name="createTableSqlStr">新建数据表SQL语句</param>
        /// <returns></returns>
        public static bool InsertData(DataTable dt, string tableName, string createTableSqlStr)
        {
            bool result = false;
            try
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
                {
                    sbc.BatchSize = dt.Rows.Count;
                    sbc.BulkCopyTimeout = 180;
                    sbc.DestinationTableName = tableName;
                    foreach (DataColumn item in dt.Columns)
                    {
                        sbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                    }
                    sbc.WriteToServer(dt);
                    result = true;
                }
            }
            catch (Exception e)
            {
                if (e.InnerException is SqlException)
                {
                    if (e.InnerException.Message == string.Format("对象名 '{0}' 无效。", tableName))
                    {
                        if (Execute(createTableSqlStr))
                        {
                            result = InsertData(dt, tableName);
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 执行无返回结果的SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public static bool Execute(string sqlStr)
        {
            bool result = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sqlStr, conn);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                    result = true;
                }
            }
            catch (Exception e)
            {
                //TODO
            }
            return result;
        }
    }
}
